﻿@using System.Configuration
@using System.Data.SqlClient
@{
    if ((Request.QueryString.Count > 0) && (Request.QueryString["RPID"] != null) && (Request.QueryString["MM"] != null) && (Request.QueryString["TYPE"] != null))
    {
        
        string reportingPeriodId = Request.QueryString["RPID"].Trim();
        string mm = Request.QueryString["MM"].Trim(); 
        string claimType = Request.QueryString["TYPE"].Trim().ToUpperInvariant();

        switch (claimType)
        {
            case "CUR":
                claimType = "CURRENT";
                break;
            case "ARR":
                claimType = "ARREAR";
                break;
        }
        
        DateTime mmDateTime = DateTime.ParseExact(mm, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);

        string fileName = string.Format("Export_To_SAP_RP{0}_{1}.csv", reportingPeriodId, mmDateTime.ToString("MMM"));

        System.Data.DataSet dsDownloadClaims = null;
        System.Text.StringBuilder sb = new System.Text.StringBuilder();


        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
        {
            System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
            dsDownloadClaims = new System.Data.DataSet("Claims_DataSet");

            cmd.CommandText = "SELECT 'Export_To_SAP_' + CONVERT(VARCHAR,YEAR(EndDate)) + SUBSTRING(UPPER(DATENAME([MONTH], EndDate)), 1, 3) AS 'FileName', * FROM ReportingPeriod WHERE Id = @RPID;";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@RPID", reportingPeriodId);
            da.Fill(dsDownloadClaims, "ReportingPeriod");
            if (dsDownloadClaims.Tables["ReportingPeriod"].Rows.Count > 0)
            {
                fileName = string.Format("{0}_{1}_{2}_Details.csv", 
                    dsDownloadClaims.Tables["ReportingPeriod"].Rows[0]["FileName"].ToString(), 
                    claimType,
                    DateTime.Now.ToString("yyyyMMdd_HHmm"));
            }
            
            cmd.CommandText =
@"SELECT  B2E.NameLegal,c.EntryOwner AS 'B2E_ID', 
        CONVERT(VARCHAR, c.ClaimDate, 103) AS 'ClaimDate',
        c.ShiftType,
        SUBSTRING(REPLACE(CONVERT(VARCHAR,c.StartWorkTime, 108), ':',''), 1, 4) AS 'StartWorkTime',
        SUBSTRING(REPLACE(CONVERT(VARCHAR,c.EndWorkTime, 108), ':',''), 1, 4) AS 'EndWorkTime',
        SUBSTRING(REPLACE(CONVERT(VARCHAR,c.StartOverTime, 108), ':',''), 1, 4) AS 'StartOverTime',
        SUBSTRING(REPLACE(CONVERT(VARCHAR,c.EndOverTime, 108), ':',''), 1, 4) AS 'EndOverTime',
        c.OT1_5,
        c.OT2_0,
        CONVERT(INT, c.PH) AS 'PH', 
        CONVERT(INT, c.RD4) AS 'RD4', 
        CONVERT(INT, c.RD8) AS 'RD8',
        c.AlwMeal,
        c.AlwShft,
        c.Remark
FROM    ReportingPeriodClaimant rpc
LEFT JOIN 
        Claim c
        ON rpc.EntryOwner = c.EntryOwner
LEFT JOIN 
        B2E
        ON rpc.EntryOwner = B2E.EmployeeMaster
WHERE   rpc.ReportingPeriodId = (SELECT Id FROM ReportingPeriod WHERE Locked = 1)
        AND c.ClaimDate >= @SelectedMonth AND c.ClaimDate < DATEADD(MONTH, 1, @SelectedMonth) 
        AND rpc.ClaimPeriod = @SelectedMonth
        AND c.Status = 6
        AND c.ApproveDate < (SELECT EndDate FROM ReportingPeriod WHERE Locked = 1);";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@SelectedMonth", mmDateTime.ToString("yyyy-MM-dd"));

            da.Fill(dsDownloadClaims, "Claims");
            System.Data.DataTable dt = dsDownloadClaims.Tables["Claims"];
            
            if (dt.Rows.Count > 0)
            {
                int rowCount = 0;

                // Write header
                sb.AppendLine("Name\tB2E_ID\tClaim_Date\tShift_Type\tOH_Start\tOH_End\tOT_Start\tOT_End\tOT1_5\tOT2_0\tPH\tRD4\tRD8\tAlwMeal\tAlwShft\tRemark");
                //foreach (System.Data.DataColumn column in dt.Columns)
                //{
                //    sb.Append(column.ColumnName);
                //    sb.Append('\t');
                //}
                //sb.Remove(sb.Length - 1, 1);
                //sb.AppendLine();

                // Write body
                foreach (System.Data.DataRow row in dt.Rows)
                {
                    rowCount++;

                    foreach (System.Data.DataColumn column in dt.Columns)
                    {
                        sb.Append(row[column].ToString());
                        sb.Append('\t');
                    }
                    sb.Remove(sb.Length - 1, 1);

                    if (rowCount != dt.Rows.Count)
                    {
                        sb.AppendLine();
                    }
                }
            } // end if (dt.Rows.Count > 0)
            
        }

        using (MemoryStream ms = new MemoryStream())
        {
            using (System.IO.StreamWriter sw = new StreamWriter(ms, System.Text.Encoding.Unicode))
            {
                sw.AutoFlush = true;
                sw.Write(sb.ToString());
                sw.Close();
            }

            Response.Clear();
            Response.ContentType = "application/octet-stream";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename=\"{0}\"", fileName));
            Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
            Response.BinaryWrite(ms.ToArray());
            Response.End();
        }
    }
}